#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/12/6 17:53
# @Author : 闫峰
# @File    : sku_filter.py
import datetime
import json

import requests

from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
import os
import threading
import time
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.mongodb_utils import MongoDBLink
from utils.times_tool.time_control import count_time

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class SkuFilter:
    db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
    # 定义mongodb

    def __init__(self,strategy_id):
        self.strategy_id = strategy_id
        # 查询策略
        get_stragety = """

        SELECT
            a.`status`,
            a.product_status,
            a.min_batch,
            a.max_batch,
            a.min_one_sides,
            a.max_one_sides,
            a.min_three_sides,
            a.max_three_sides,
            a.min_package_weight,
            a.max_package_weight,
            a.coefficient,
            a.min_volume_weight,
            a.max_volume_weight,
            a.min_rate,
            a.max_rate,
            a.purchase_price_min,
            a.purchase_price_max,
            a.review_start_time,
            a.review_end_time,
            a.review_day_min,
            a.review_day_max,
            a.prohibit_platform_type,
            a.is_brand,
            a.infringe_type,
            a.product_type,
            GROUP_CONCAT( b.product_attribute_id ),
            a.delivery_warehouse,
            a.law_firm_type,
            a.is_group
        FROM
            test_zhcxkj_center_strategy_comprehensive.t_shopee_product_rules_template a
            LEFT JOIN test_zhcxkj_center_strategy_comprehensive.t_shopee_product_attribute_filtering b ON a.id = b.product_rules_template_id 
        WHERE
            a.id = %s 
            AND b.is_deleted=0
        GROUP BY
            a.id
        """
# 这里有个问题：当模板的物流属性修改后，不能读取到最新的物流属性配置
        stragety = self.db.fetch_all_tuple(get_stragety,(strategy_id,))
        if not len(stragety):
            raise
        if stragety[0][0]:
            raise
        self.status = stragety[0][0]
        self.product_status= list(map(int,stragety[0][1].split(",")))
        self.min_batch= stragety[0][2]
        self.max_batch= stragety[0][3]
        self.min_one_sides= stragety[0][4]
        self.max_one_sides= stragety[0][5]
        self.min_three_sides= stragety[0][6]
        self.max_three_sides= stragety[0][7]
        self.min_package_weight= stragety[0][8]
        self.max_package_weight= stragety[0][9]
        self.coefficient= stragety[0][10]
        self.min_volume_weight= stragety[0][11]
        self.max_volume_weight= stragety[0][12]
        self.min_rate= stragety[0][13]
        self.max_rate= stragety[0][14]
        self.purchase_price_min= stragety[0][15]
        self.purchase_price_max= stragety[0][16]
        self.review_start_time= stragety[0][17]
        self.review_end_time= stragety[0][18]
        self.review_day_min= stragety[0][19]
        self.review_day_max= stragety[0][20]
        self.prohibit_platform_type= stragety[0][21]
        if self.prohibit_platform_type:
            platform_type_set = set(self.prohibit_platform_type.split(","))
            get_no_sale_sku_list = """
                        				SELECT sku
        				FROM test_zhcxkj_center_product.rb_product_no_sale
        				WHERE platform_type in %s
                        """
            sku_list = self.db.fetch_all_tuple(get_no_sale_sku_list, (platform_type_set,))
            self.no_sale_sku_list = list(map(lambda x: x[0], sku_list))
        self.is_brand= stragety[0][22]
        self.infringe_type= stragety[0][23]
        self.product_type= stragety[0][24]
        self.product_attribute_id_list = list(map(int,stragety[0][25].split(",")))
        # self.delivery_warehouse_list = list(map(int, stragety[0][26].split(",")))
        self.law_firm_type = stragety[0][27]
        if self.law_firm_type == 0:
            self.law_firm_type = [-1, 0]
        elif self.law_firm_type == 1:
            self.law_firm_type = [1]
        else:
            self.law_firm_type = [-1, 0, 1]
        self.is_group = stragety[0][28]
        if self.is_group == 0:
            self.is_group = [0]
        elif self.is_group == 1:
            self.is_group = [1]
        else:
            self.is_group = [0,1]

    @count_time
    def sku_filter(self):
        # 分批次拉取数据源 在线的listing数据

        sql1 = f"""
        SELECT
            rp.id, 
            rp.customer_id,
            rp.sku,
            rp.product_group_sku, -- 是否变体 3
            rp.last_buy_price,  -- 采购价 4 
            rp.is_group,  -- 是否组合产品 5
            rp.product_state, -- 产品状态 6
            rp.product_property,  -- 侵权风险  7
            rp.brand_id,  -- 是否品牌 8 
            rp.with_battery, -- 物流属性 9 
            rp.min_sale_num , -- 起批量 10
        CASE WHEN (rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0) THEN rp.pack_length ELSE rp.length END AS length, -- 长 11
        CASE WHEN (rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0) THEN rp.pack_width ELSE rp.width  END AS width,-- 款 12
        CASE WHEN (rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0 )THEN rp.pack_height ELSE rp.height END AS height ,-- 高 13
        CASE WHEN rp.pack_weight=0 THEN rp.gross_weight ELSE rp.pack_weight END	AS pack_weight, -- 重量 14
        rpa.check_time,  -- 终审时间 15
        rp.is_group, -- 产品类型 16
        rp.default_local_warehouse, -- 默认发货仓库 17
        rp.has_law_firm_agent -- 律所标识 18
        FROM `test_zhcxkj_center_product`.rb_product rp
        JOIN `test_zhcxkj_center_product`.rb_product_admin rpa ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku AND rpa.check_status = 1
        AND rp.delete_status=0
        WHERE rp.id>%s
        AND rp.customer_id = 1
        order by id 
        LIMIT 1000
        """
        id_index = 0
        sku_set_total = set()
        while True:
            print(f"index={id_index}")
            data_list = self.db.fetch_all_tuple(sql1, (id_index,))
            if not len(data_list):
                break
            id_index = data_list[-1][0]
            data_set = set(data_list)

            if not data_set:
                continue
            # 参与过滤的sku必须是终审通过的sku
            data_set = set(filter(lambda x: x[16]==0, data_set))
            if not data_set:
                continue
            # 产品状态 product_state  0.正常；1.仅批量；2.停产；3.暂时缺货；4.锁定；5.清库
            data_set = set(filter(lambda x:x[6] in self.product_status,data_set))
            if not data_set:
                continue
            # 物流属性
            data_set = set(filter(lambda x: x[9] in self.product_attribute_id_list, data_set))
            if not data_set:
                continue
            # # 发货仓库(非必填)
            # if self.delivery_warehouse_list :
            #     data_set = set(filter(lambda x: x[17] in self.delivery_warehouse_list, data_set))
            # 律所标识(非必填)
            if self.law_firm_type :
                data_set = set(filter(lambda x: x[18] in self.law_firm_type, data_set))
            if not data_set:
                continue
            # 起批量  min_sale_num
            if self.min_batch or self.max_batch:
                data_set = set(filter(lambda x: self.min_batch <= x[10] <= self.max_batch, data_set))
            if not data_set:
                continue
            # 单边 每条边都是符合
            if self.min_one_sides or self.max_one_sides:
                data_set = set(filter(lambda x: self.min_one_sides <= x[11] <= self.max_one_sides and self.min_one_sides <= x[12] <= self.max_one_sides and self.min_one_sides <= x[13] <= self.max_one_sides, data_set))
            if not data_set:
                continue
            # 三边之和
            if self.min_three_sides or self.max_three_sides:
                data_set = set(filter(lambda x: self.min_three_sides <= (x[11]+ x[12] + x[13]) <= self.max_three_sides, data_set))
            if not data_set:
                continue
            # 包裹重量
            if self.min_package_weight or self.max_package_weight:
                data_set = set(filter(lambda x: self.min_package_weight <= (x[14]/1000)  <= self.max_package_weight, data_set))
            if not data_set:
                continue
            # 体积重
            if self.min_volume_weight or self.max_volume_weight:
                data_set = set(filter(lambda x: self.min_volume_weight <= ((x[11] * x[12] * x[13]) / self.coefficient) <= self.max_volume_weight, data_set))
            if not data_set:
                continue
            # 抛率
            if self.min_rate or self.max_rate:
                try:
                    data_set = set(filter(lambda x: x[14], data_set))
                    data_set = set(filter(lambda x: self.min_rate <= ((x[11] * x[12] * x[13]) / self.coefficient / x[14]*1000) <= self.max_rate, data_set))
                except:
                    print()
                    raise
            if not data_set:
                continue
            # 采购价
            if self.purchase_price_min or self.purchase_price_max:
                data_set = set(filter(lambda x: self.purchase_price_min <= x[4] <= self.purchase_price_max, data_set))
            if not data_set:
                continue
            # 侵权类型
            # 1非仿牌 2仿牌 3禁售
            if self.infringe_type:
                data_set = set(filter(lambda x: x[7] ==self.infringe_type, data_set))
            if not data_set:
                continue
            # 产品类型
            # 0 全部 1 单体 2多变体
            if self.product_type:
                if self.product_type ==1:
                    data_set = set(filter(lambda x: not bool(x[3]), data_set))
                elif self.product_type == 2:
                    data_set = set(filter(lambda x: bool(x[3]), data_set))
            if not data_set:
                continue
            # 是否组合产品
            # 0 非组合 1 组合 2全部
            if self.is_group :
                data_set = set(filter(lambda x: x[5] in self.is_group, data_set))
            if not data_set:
                continue
            # 是否品牌产品
            # 0 全部 1 是 2 否
            if self.is_brand:
                if self.is_brand ==1:
                    data_set = set(filter(lambda x: bool(x[8]), data_set))
                elif self.is_brand ==2:
                    data_set = set(filter(lambda x: not bool(x[8]), data_set))
            if not data_set:
                continue
            # 终审时间 天数范围
            if self.review_day_min or self.review_day_max:
                pass

            if not data_set:
                continue
            # 终审时间  日期范围
            if self.review_start_time:
                data_set = set(filter(lambda x: self.review_start_time <= x[15] <= self.review_end_time, data_set))
            if not data_set:
                continue
            # 禁售平台
            if self.prohibit_platform_type:
                data_set = set(filter(lambda x: x[2] not in self.no_sale_sku_list, data_set))
            if not data_set:
                continue

            sku_set = set(map(lambda x:x[2],data_set))
            sku_set_total |= sku_set

        INFO.logger.info(f"最终筛选{len(sku_set_total)}")
        # INFO.logger.info(f"sku_set_total={sku_set_total}")
        get_actual_sku_list = """
        SELECT sku FROM `test_zhcxkj_center_strategy_comprehensive`.`t_shopee_product_rules_sku` WHERE `product_rules_template_id` = %s
        """
        actual_sku_list = self.db.fetch_all_tuple(get_actual_sku_list,(self.strategy_id,))
        actual_sku_set = set(map(lambda x:x[0], actual_sku_list))
        if sku_set_total == actual_sku_set:
            pass
        else:
            ERROR.logger.error(f"错误，差异如下")
            # ERROR.logger.error(f"{sku_set_total-actual_sku_set}")
            print(f"{sku_set_total-actual_sku_set}")
            print(f"{actual_sku_set - sku_set_total}")




if __name__ == '__main__':
    t = SkuFilter(36)
    t.sku_filter()